#include "StdAfx.h"
#include "ArticleManager.h"


CArticleManager::CArticleManager(void)
{
	//m_db.Open(thePrefs.GetDB());
}


CArticleManager::~CArticleManager(void)
{
}
int CArticleManager::Add(const Article& art) 
{
	static LPCWSTR szSQL = L"insert into km_article(classid,title,content,creator,timecreated,level,bookmark,type,hits) values (?,?,?,?,?,?,?,?,?);";
	if(art.strTitle.IsEmpty())
		return 5;
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,art.iClassID);
	pSQLStatement->Bind(2,art.strTitle);
	pSQLStatement->Bind(3,art.strContent);
	pSQLStatement->Bind(4,art.strCreator);
	pSQLStatement->Bind(5,(int)art.dtTimeCreated.GetTime());
	pSQLStatement->Bind(6,art.iLevel);
	pSQLStatement->Bind(7,art.strBookMark);
	pSQLStatement->Bind(8,art.strType);
	pSQLStatement->Bind(9,1);
	BOOL Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());

	if(Res)
	{
		m_pDB->CommitTransaction();;
		return 6;
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 7;
	}
}
int CArticleManager::Delete(const int iID) 
{
	static LPCWSTR szSQL = L"delete from km_article where id=?;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,iID);
	
	BOOL Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());

	if(Res)
	{
		m_pDB->CommitTransaction();
		return 8;
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 9;
	}
}
int CArticleManager::Update(const Article& art) 
{
	return 1;
}
Article* CArticleManager::GetArticle(const int iID) 
{
	static LPCWSTR szSQL = L"select classid,title,content,creator,timecreated,level,bookmark,editor,timeedit,timeread,hits,type,KeyWords,author from km_article where id=?;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,iID);
	CComPtr<Skylark::ISQLiteComResultSet3> pRs;
	HRESULT hr = pSQLStatement->ExecuteQuery(&pRs);
	if(!SUCCEEDED(hr) || pRs->IsEof())
		return FALSE;
	//Article art;
	if(!pRs->IsEof())
	{
		m_Art.iID = iID;
		m_Art.iClassID = pRs->GetInt(L"Classid");
		m_Art.strTitle = pRs->GetAsString(L"Title");
		m_Art.strContent = pRs->GetAsString(L"Content");
		m_Art.strCreator = pRs->GetAsString(L"Creator");
		m_Art.iLevel = pRs->GetInt(L"level");
		m_Art.strBookMark = pRs->GetAsString (L"BookMark");
		m_Art.dtTimeCreated = CTime(pRs->GetInt(L"TimeCreated"));
		m_Art.strEditor = pRs->GetAsString(L"Editor");
		m_Art.dtTimeEdit = CTime(pRs->GetInt(L"TimeEdit"));
		m_Art.dtTimeRead = CTime(pRs->GetInt(L"TimeRead"));
		m_Art.iHits = pRs->GetInt(L"Hits");
		m_Art.strType = pRs->GetAsString(L"Type");
		m_Art.strKeyWords = pRs->GetAsString(L"KeyWords");
		m_Art.strAuthor = pRs->GetAsString(L"Author");
		return &m_Art;
	}
	return NULL;
}

int CArticleManager::SetHits(const int iID) 
{
	static LPCWSTR szSQL = L"update km_article set hits=hits+1 where id=?;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,iID);
	
	BOOL Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());

	if(Res)
	{
		m_pDB->CommitTransaction();
		return 10;
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 11;
	}
}
int CArticleManager::SetLevel(const int iID,const int iLevel) 
{
	static LPCWSTR szSQL = L"update km_article set level=? where id=?;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,iLevel);
	pSQLStatement->Bind(2,iID);
	BOOL Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());

	if(Res)
	{
		m_pDB->CommitTransaction();
		return 10;
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 11;
	}
}
int CArticleManager::SetTimeRead(const int iID,const CTime& tTimeRead) 
{
	static LPCWSTR szSQL = L"update km_article set timeread=? where id=?;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,(int)tTimeRead.GetTime());
	pSQLStatement->Bind(2,iID);
	BOOL Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());

	if(Res)
	{
		m_pDB->CommitTransaction();
		return 10;
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 11;
	}
}
int CArticleManager::SetKeyWords(const int nID,const CString strKeyWords)
{
	static LPCWSTR szSQL = L"update km_article set keywords=? where id=?;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,strKeyWords);
	pSQLStatement->Bind(2,nID);
	BOOL Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());

	if(Res)
	{
		m_pDB->CommitTransaction();
		return 10;
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 11;
	}
}
int CArticleManager::SetAuthor(const int nID,const CString strAuthor)
{
	static LPCWSTR szSQL = L"update km_article set author=? where id=?;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,strAuthor);
	pSQLStatement->Bind(2,nID);
	BOOL Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());

	if(Res)
	{
		m_pDB->CommitTransaction();
		return 10;
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 11;
	}
}
int CArticleManager::SetBookmark(const int nID,const CString strRemark)
{
	static LPCWSTR szSQL = L"update km_article set BookMark=? where id=?;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,strRemark);
	pSQLStatement->Bind(2,nID);
	BOOL Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());

	if(Res)
	{
		m_pDB->CommitTransaction();
		return 10;
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 11;
	}
}
int CArticleManager::SetCreateDt(const int nID,const CTime& tCreate)
{
	static LPCWSTR szSQL = L"update km_article set TimeCreated=? where id=?;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,(int)tCreate.GetTime());
	pSQLStatement->Bind(2,nID);
	BOOL Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());

	if(Res)
	{
		m_pDB->CommitTransaction();
		return 10;
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 11;
	}
}
int	CArticleManager::AddTag(const int nID,const int nTagId)
{
	static LPCWSTR szSQL1 = L"Select count(id) from KM_Article_Tags where ID=? and TagId=?;";
	
	static LPCWSTR szSQL = L"insert into KM_Article_Tags(ID,TagID) values(?,?);";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->PrepareStatement(szSQL1,&pSQLStatement);
	pSQLStatement->Bind(1,nID);
	pSQLStatement->Bind(2,nTagId);
	CComPtr<Skylark::ISQLiteComResultSet3> pRs;
	HRESULT hr = pSQLStatement->ExecuteQuery(&pRs);
	if(!SUCCEEDED(hr) || pRs->IsEof())
		return 11;
	int nCount = pRs->GetInt(0);
	if(nCount > 0)
		return 10;
	pSQLStatement.Release();
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,nID);
	pSQLStatement->Bind(2,nTagId);
	BOOL Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());

	if(Res)
	{
		m_pDB->CommitTransaction();
		return 10;
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 11;
	}
}
int CArticleManager::AddTag(const int nID,const CString strTag)
{
	static LPCWSTR szSQL1 = L"Select TagId from KM_Tags where TagName=?;";
	static LPCWSTR szSQL2 = L"Insert into KM_Tags(TagName) values(?);";

	static LPCWSTR szSQL = L"insert into KM_Article_Tags(ID,TagID) values(?,?);";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->PrepareStatement(szSQL1,&pSQLStatement);
	pSQLStatement->Bind(1,strTag);
	CComPtr<Skylark::ISQLiteComResultSet3> pRs;
	HRESULT hr = pSQLStatement->ExecuteQuery(&pRs);
	int nTagId = 0;
	BOOL Res;
	if(SUCCEEDED(hr) && !pRs->IsEof())
	{
		nTagId = pRs->GetInt(0);
	}
	else
	{
		pSQLStatement.Release();
		m_pDB->BeginTransaction();
		m_pDB->PrepareStatement(szSQL2,&pSQLStatement);
		pSQLStatement->Bind(1,strTag);
		Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());
		if(Res)
		{
			m_pDB->CommitTransaction();
			pSQLStatement.Release();
			m_pDB->PrepareStatement(szSQL1,&pSQLStatement);
			pSQLStatement->Bind(1,strTag);
			pRs.Release();
			hr = pSQLStatement->ExecuteQuery(&pRs);
			if(SUCCEEDED(hr) && !pRs->IsEof())
			{
				nTagId = pRs->GetInt(0);
			}
		}
		else
		{
			m_pDB->RollbackTransaction();
		}
	}
	pSQLStatement.Release();
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,nID);
	pSQLStatement->Bind(2,nTagId);
	Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());

	if(Res)
	{
		m_pDB->CommitTransaction();
		return 10;
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 11;
	}
}
int CArticleManager::CreateTag(const CString strTag)
{
	static LPCWSTR szSQL2 = L"Insert into KM_Tags(TagName) values(?);";
	static LPCWSTR szSQL1 = L"Select TagId from KM_Tags where TagName=?;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL2,&pSQLStatement);
	pSQLStatement->Bind(1,strTag);
	BOOL Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());
	if(Res)
	{
		m_pDB->CommitTransaction();
		pSQLStatement.Release();
		m_pDB->PrepareStatement(szSQL1,&pSQLStatement);
		pSQLStatement->Bind(1,strTag);
		CComPtr<Skylark::ISQLiteComResultSet3> pRs;
		HRESULT hr = pSQLStatement->ExecuteQuery(&pRs);
		if(SUCCEEDED(hr) && !pRs->IsEof())
		{
			return pRs->GetInt(0);
		}
		return 10;
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 11;
	}
}

ArticleList* CArticleManager::GetArticleList(const int iClassID)
{
	
	static LPCWSTR szSQL = L"select id,classid,title,timecreated,level,timeread,hits,type from km_article where classid=? order by timeread;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,iClassID);
	CComPtr<Skylark::ISQLiteComResultSet3> pRs;
	HRESULT hr = pSQLStatement->ExecuteQuery(&pRs);
	if(!SUCCEEDED(hr) || pRs->IsEof())
		return NULL;
	ArticleList* list = new ArticleList();
	while(!pRs->IsEof())
	{
		Article* art =new Article();
		art->iID = pRs->GetInt(L"ID");
		art->iClassID = pRs->GetInt(L"ClassID");
		art->strTitle =pRs->GetAsString(L"Title");
		
		art->iLevel = pRs->GetInt(L"Level");
		
		art->dtTimeCreated = CTime(pRs->GetInt(L"TimeCreated"));
		
		art->dtTimeRead = CTime(pRs->GetInt(L"TimeRead"));
		art->iHits = pRs->GetInt(L"Hits");
		art->strType = pRs->GetAsString(L"Type");
		
		list->AddTail(art);
		pRs->NextRow();
	}
	return list;
}
ArticleList* CArticleManager::SearchArticleList(const CString strSearch)
{
	CString szSQL = L"select id,title,content,creator,Author,timecreated,level,bookmark,KeyWords,editor,timeedit,timeread,hits,type from km_article where content like '%";
	szSQL +=  + strSearch;
	szSQL += L"%' or title like '%";
	szSQL += strSearch;
	szSQL += L"%' or author like '%";
	szSQL += strSearch ;
	szSQL += L"%' or BookMark like '%";
	szSQL += strSearch;
	szSQL += L"%' or type like '%";
	szSQL += strSearch;
	szSQL += L"%' order by timeread;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	//pSQLStatement->Bind(1,iClassID);
	CComPtr<Skylark::ISQLiteComResultSet3> pRs;
	HRESULT hr = pSQLStatement->ExecuteQuery(&pRs);
	if(!SUCCEEDED(hr) || pRs->IsEof())
		return NULL;
	ArticleList* list = new ArticleList();
	while(!pRs->IsEof())
	{
		Article* art =new Article();
		art->iID = pRs->GetInt(L"ID");
		art->strTitle = pRs->GetAsString(L"Title");
		art->strContent = pRs->GetAsString(L"Content");
		art->strCreator = pRs->GetAsString(L"Creator");
		art->strAuthor = pRs->GetAsString(L"Author");
		art->iLevel = pRs->GetInt(L"Level");
		art->strBookMark = pRs->GetAsString (L"BookMark");
		art->strKeyWords = pRs->GetAsString (L"KeyWords");
		
		art->dtTimeCreated = CTime(pRs->GetInt(L"TimeCreated"));
		art->strEditor = pRs->GetAsString(L"Editor");
		art->dtTimeEdit = CTime(pRs->GetInt(L"TimeEdit"));
		art->dtTimeRead = CTime(pRs->GetInt(L"TimeRead"));
		art->iHits = pRs->GetInt(L"Hits");
		art->strType = pRs->GetAsString(L"Type");
		list->AddTail(art);
		pRs->NextRow();
	}
	return list;
}
int  CArticleManager::MoveTo(const int iClassID,const int iID)
{
	static LPCWSTR szSQL = L"update km_article set classid=? where id=?;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,iClassID);
	pSQLStatement->Bind(2,iID);
	BOOL Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());

	if(Res)
	{
		m_pDB->CommitTransaction();
		return 10;
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 11;
	}
}

TagList* CArticleManager::GetTags()
{
	static LPCWSTR szSQL = L"select t.TagName,t.TagID,count(*) as Tags from KM_Article_Tags a,KM_Tags t where a.TagID=t.TagID group by t.TagName,t.TagID union select t.TagName,t.TagID ,0 as Tags from KM_Tags t where t.TagID not in(select tagid from km_article_tags) ;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	CComPtr<Skylark::ISQLiteComResultSet3> pRs;
	HRESULT hr = pSQLStatement->ExecuteQuery(&pRs);
	if(!SUCCEEDED(hr) || pRs->IsEof())
		return NULL;
	TagList* list = new TagList();
	while(!pRs->IsEof())
	{
		Tags* tg =new Tags();
		tg->nTag = pRs->GetInt(L"t.TagID");
		tg->strTag = pRs->GetAsString(L"t.TagName");
		tg->nCount  =pRs->GetInt(L"Tags");
		
		list->AddTail(tg);
		pRs->NextRow();
	}
	return list;
}
TagList* CArticleManager::GetAllTags()
{
	static LPCWSTR szSQL = L"select TagName,TagID from KM_Tags ;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	CComPtr<Skylark::ISQLiteComResultSet3> pRs;
	HRESULT hr = pSQLStatement->ExecuteQuery(&pRs);
	if(!SUCCEEDED(hr) || pRs->IsEof())
		return NULL;
	TagList* list = new TagList();
	while(!pRs->IsEof())
	{
		Tags* tg =new Tags();
		tg->nTag = pRs->GetInt(L"TagID");
		tg->strTag = pRs->GetAsString(L"TagName");
		//tg->nCount  =pRs->GetInt(L"Tags");
		
		list->AddTail(tg);
		pRs->NextRow();
	}
	return list;
}
ArticleList* CArticleManager::GetArticleListByTag(const int nTagID)
{
	static LPCWSTR szSQL = L"select a.id,classid,title,timecreated,level,timeread,hits,type from km_article a,KM_Article_Tags t where a.id=t.id and t.TagID=? order by timeread;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,nTagID);
	CComPtr<Skylark::ISQLiteComResultSet3> pRs;
	HRESULT hr = pSQLStatement->ExecuteQuery(&pRs);
	if(!SUCCEEDED(hr) || pRs->IsEof())
		return NULL;
	ArticleList* list = new ArticleList();
	while(!pRs->IsEof())
	{
		Article* art =new Article();
		art->iID = pRs->GetInt(L"ID");
		art->iClassID = pRs->GetInt(L"ClassID");
		art->strTitle =pRs->GetAsString(L"Title");
		
		art->iLevel = pRs->GetInt(L"Level");
		
		art->dtTimeCreated = CTime(pRs->GetInt(L"TimeCreated"));
		
		art->dtTimeRead = CTime(pRs->GetInt(L"TimeRead"));
		art->iHits = pRs->GetInt(L"Hits");
		art->strType = pRs->GetAsString(L"Type");
		
		list->AddTail(art);
		pRs->NextRow();
	}
	return list;
}
CSimpleArray<int> CArticleManager::GetArtTagList(const int nID)
{
	static LPCWSTR szSQL = L"select tagid from KM_Article_Tags where id=?;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->PrepareStatement(szSQL,&pSQLStatement);

	pSQLStatement->Bind(1,nID);
	CComPtr<Skylark::ISQLiteComResultSet3> pRs;
	HRESULT hr = pSQLStatement->ExecuteQuery(&pRs);
	CSimpleArray<int> list;
	if(!SUCCEEDED(hr) || pRs->IsEof())
		return list;
	
	while(!pRs->IsEof())
	{	
		list.Add(pRs->GetInt(L"TagID"));
		pRs->NextRow();
	}
	return list;
}

int CArticleManager::RenameTag(const int nTagID,const CString strTag)
{
	static LPCWSTR szSQL2 = L"update KM_Tags set TagName=? where TagID=?;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL2,&pSQLStatement);
	pSQLStatement->Bind(1,strTag);
	pSQLStatement->Bind(2,nTagID);
	BOOL Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());
	if(Res)
	{
		m_pDB->CommitTransaction();
		return 10;
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 11;
	}
}

int CArticleManager::DeleteTag(const int nTagID)
{
	static LPCWSTR szSQL2 = L"delete from KM_Tags where TagID=?;";
	static LPCWSTR szSQL1 = L"delete from KM_Article_Tags where TagID=?;";
	CComPtr<ISQLiteComStatement3> pSQLStatement;
	m_pDB->BeginTransaction();
	m_pDB->PrepareStatement(szSQL2,&pSQLStatement);
	pSQLStatement->Bind(1,nTagID);
	
	BOOL Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());
	if(Res)
	{
		pSQLStatement.Release();
		m_pDB->PrepareStatement(szSQL1,&pSQLStatement);
		pSQLStatement->Bind(1,nTagID);
	
		Res = SUCCEEDED(pSQLStatement->ExecuteUpdate());
		if(Res)
		{
			m_pDB->CommitTransaction();
			return 10;
		}
		else
		{
			m_pDB->RollbackTransaction();
			return 11;
		}
	}
	else
	{
		m_pDB->RollbackTransaction();
		return 11;
	}
}